﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CMP.ERP.Helper;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using CMP.ERP.Model;

namespace CMP.ERP.DAL
{
    public class CommodityTypeDAL
    {
        /// <summary>
        /// 实例化sqlhelper帮助类
        /// </summary>
        SQLHelper helper = new SQLHelper();

        string connSql = "Data Source=169.254.4.161;Initial Catalog=FourGroupERP;User ID=sa;Pwd=111111";
        /// <summary>
        /// 获取商品品牌信息
        /// </summary>
        /// <returns></returns>
        public List<CommodityType> GetCommandTypeAll()
        {
            string sql = "select CommodityType.CommodityType_Id,CommodityType_Name,COUNT(Commodity.CommodityType_Id) num from CommodityType left join Commodity on Commodity.CommodityType_Id=CommodityType.CommodityType_Id group by CommodityType.CommodityType_Id,CommodityType_Name";
            SqlDataAdapter adapter = new SqlDataAdapter(sql,connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<CommodityType> list = new List<CommodityType>();
            foreach (DataRow item in table.Rows)
            {
                CommodityType c = new CommodityType();
                c.CommodityType_Id = Convert.ToInt32(item["CommodityType_Id"]);
                c.CommodityType_Name = item["CommodityType_Name"].ToString();
                c.num = Convert.ToInt32(item["num"]);
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 根据商品品牌获取商品信息
        /// </summary>
        /// <returns></returns>
        public List<Commodity> GetCommodityByCtid(int id)
        {
            string sql = "select ct.CommodityType_Id,Commodity_Id,Commodity_Name,Commodity_number,Commodity_Weight,Commodity_Remark,ct.CommodityType_Name from Commodity c join CommodityType ct on c.CommodityType_Id=ct.CommodityType_Id where c.CommodityType_Id="+id+"";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            List<Commodity> list = new List<Commodity>();
            foreach (DataRow item in table.Rows)
            {
                Commodity c = new Commodity();
                c.Commodity_Id = Convert.ToInt32(item["Commodity_Id"]);
                c.Commodity_Name = (item["Commodity_Name"]).ToString();
                c.Commodity_number = Convert.ToInt32(item["Commodity_number"]);
                c.Commodity_Remark = (item["Commodity_Remark"]).ToString();
                c.Commodity_Price = Convert.ToInt32(item["Commodity_Weight"]);
                c.CommodityType_Name = item["CommodityType_Name"].ToString();
                c.CommodityType_Id = Convert.ToInt32(item["CommodityType_Id"]);
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 添加商品品牌
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int AddCommodityType(CommodityType ct)
        {
            string sql = string.Format("insert into CommodityType values('"+ct.CommodityType_Name+"')");
            return helper.ExecNonquery(sql);
        }
        /// <summary>
        /// 根据id获取单行品牌信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CommodityType GetCtById(int id)
        {
            string sql = "select CommodityType_Id,CommodityType_Name from CommodityType where CommodityType_Id=" + id + "";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connSql);
            DataTable table = new DataTable();
            adapter.Fill(table);
            CommodityType c = new CommodityType();
            foreach (DataRow item in table.Rows)
            {
                c.CommodityType_Id = Convert.ToInt32(item["CommodityType_Id"]);
                c.CommodityType_Name = item["CommodityType_Name"].ToString();
            }
            return c;
        }
        /// <summary>
        /// 更新商品品牌信息
        /// </summary>
        /// <param name="ct"></param>
        /// <returns></returns>
        public int EditCommodityType(CommodityType ct)
        {
            string sql = "update CommodityType set CommodityType_Name='" + ct.CommodityType_Name + "' where CommodityType_Id=" + ct.CommodityType_Id + "";
            return helper.ExecNonquery(sql);
        }
        /// <summary>
        /// 删除商品品牌
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelCommodityType(int id)
        {
            string sql = " delete from CommodityType where CommodityType_Id=" + id + "";
            return helper.ExecNonquery(sql);
        }
    }
}
